﻿-------------strat Dirty read (Tuyen)-----------------------
if object_id('sp_TraCuuTuyen','p')
	is not null
drop proc sp_TraCuuTuyen
go
create proc sp_TraCuuTuyen
@matuyen nvarchar(50),@tentuyen nvarchar(50),@noidi nvarchar(50), @noiden nvarchar(50), @dodai nvarchar(50),
@trangthai nvarchar(50), @hesogiave nvarchar(50), @mota nvarchar(max), @malichtrinh nvarchar(50)
as
begin tran
	--set tran isolation level read uncommitted
	set tran isolation level read committed
	declare @query nvarchar(600), @flag int
	set @query ='select * from tuyen'
	set @flag = 0
		
	if(@matuyen<>0)
		begin
			set @query = @query +' where matuyen = ' + @matuyen
			set @flag = 1
		end
		
	if(@tentuyen<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and tentuyen like ' + 'N''%' + @tentuyen + '%''' 
			else
				begin					
					set @query = @query +' where tentuyen like ' + 'N''%' + @tentuyen + '%''' 
					set @flag = 1
				end
		end
			
	if(@noidi<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and noidi ='+N'@noidi'
			else
				begin
				set @query = @query +' where noidi ='+N'@noidi'
					set @flag = 1
				end
		end

	if(@noiden<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and noiden ='+N'@noiden'
			else
				begin
				set @query = @query +' where noiden ='+N'@noiden'
					set @flag = 1
				end
		end
	if(@dodai<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and dodai = ' + @dodai
			else
				begin
				set @query = @query +' where dodai =' + @dodai
					set @flag = 1
				end
		end
	if(@hesogiave<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and hesogiave = ' + @hesogiave
			else
				begin
				set @query = @query +' where hesogiave =' + @hesogiave
					set @flag = 1
				end
		end
	if(@trangthai<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and trangthai ='+ @trangthai
			else
				begin
				set @query = @query +' where trangthai ='+ @trangthai
					set @flag = 1
				end
		end
			
	if(@mota<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and @mota like ' + N'@mota'
			else
				begin
				set @query = @query +' where mota like' + N'@mota'
					set @flag = 1
				end
		end
	if(@malichtrinh<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and malichtrinh = ' + @malichtrinh
			else
				begin
				set @query = @query +' where malichtrinh =' + @malichtrinh
					set @flag = 1
				end
		end
	print(@query)
	exec(@query)
	if(@@error<> 0)begin
		rollback tran
		return
	end
commit

exec sp_TraCuuTuyen '','HCM','','','','','','',''






--------------------------------------------------------

if object_id('sp_ThemTuyen', 'p') is not null
drop proc sp_ThemTuyen
go
create proc sp_ThemTuyen
@matuyen int, @tentuyen nvarchar(50),
@noidi nvarchar(50),@noiden nvarchar(50), 
@dodai int, @trangthai int, 
@hesogiave float, @mota nvarchar(MAX) , @malichtrinh int
as 
begin tran
--set tran isolation level read uncommitted
set tran isolation level read committed
	if (@matuyen = 0)
        insert into tuyen
		values(@tentuyen, @noidi, @noiden, @dodai, @trangthai, @hesogiave, @mota, @malichtrinh)
    else
		update tuyen
		set tentuyen = @tentuyen,
			noidi = @noidi,
			noiden = @noiden,
			dodai = @dodai,
			trangthai = @trangthai,
			hesogiave = @hesogiave,
			malichtrinh = @malichtrinh,
			mota = @mota
		where matuyen = @matuyen
		waitfor delay'0:00:10'
        if(@@error <> 0)begin
                rollback tran
                return
        end
	rollback 
commit tran

exec sp_ThemTuyen '0','abc','xczvxvz','xcvzvcxvz','30','1','20','sdfadfs','1'
-------------end Dirty read (Tuyen)-----------------------

----------------------------------------------------------
if object_id('sp_TraCuuTuyenTheoMa', 'p') is not null
drop proc sp_TraCuuTuyenTheoMa
go
create proc sp_TraCuuTuyenTheoMa
        @matuyen int
as
begin
        select * from tuyen
        where matuyen = @matuyen
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_TraCuuTuyenTheoMa '2'
-------------start Lost update (Tuyen)-----------------------
if object_id('sp_CapNhatHeSoGiaVe', 'p') is not null
drop proc sp_CapNhatHeSoGiaVe
go
create proc  sp_CapNhatHeSoGiaVe
@matuyen int, @heso float
as
begin  tran
	declare  @hesogiave float
	set  @hesogiave =  (select  hesogiave from  tuyen with (updlock) where matuyen=@matuyen)
	--set  @hesogiave =  (select  hesogiave from  tuyen where matuyen=@matuyen)
	waitfor  delay  '0:00:10'
	set  @hesogiave=@hesogiave + @heso
	update  tuyen
	set  hesogiave = @hesogiave
	where  matuyen=@matuyen
commit
-------------end Lost update (Tuyen)-----------------------


-------------start Unreapeatable read (Loai xe)-----------------------
if object_id('sp_TraCuuLoaiXeTheoTen', 'p') is not null
drop proc sp_TraCuuLoaiXeTheoTen
go
create proc sp_TraCuuLoaiXeTheoTen
@tenloaixe nvarchar(50)
as
begin tran
	set tran isolation level repeatable read
	select * from loaixe where tenloaixe like '%'+@tenloaixe+'%'
	
	waitfor delay '0:00:5'
	
	select * from loaixe where tenloaixe like '%'+@tenloaixe+'%'
	
commit
-------------end Unreapeatable read (Loai xe)-----------------------

-------------start Phantom (Loai xe)-----------------------
if object_id('sp_TraCuuLoaiXeTheoTen', 'p') is not null
drop proc sp_TraCuuLoaiXeTheoTen
go
create proc sp_TraCuuLoaiXeTheoTen
@tenloaixe nvarchar(50)
as
begin tran
	set tran isolation level serializable 
	select * from loaixe where tenloaixe like N'%'+@tenloaixe+'%'	
	 
	waitfor delay '0:00:6'
	
	select * from loaixe where tenloaixe like N'%'+@tenloaixe+'%'	
commit


--exec sp_TraCuuLoaiXeTheoTen N'Loại'
-------------end Phantom (Loai xe)-----------------------


-------------Start Conversion Deadlock  (Kho Hang)-----------------------
if object_id('sp_TraCuuKhoHangTheoTen', 'p') is not null
drop proc sp_TraCuuKhoHangTheoTen
go
create proc sp_TraCuuKhoHangTheoTen
@tenkho nvarchar(50)
as
begin tran
	set tran isolation level serializable
	select * from khohang with (readcommitted)

	where tenkho like N'%'+@tenkho+'%'
	 
	waitfor delay '0:00:5'
	
	update khohang 
	set mota=''
	where tenkho like N'%'+@tenkho+'%'
commit

exec sp_TraCuuKhoHangTheoTen C
-------------end Conversion Deadlock  (Kho Hang)-----------------------

-------------Start Conversion Deadlock  (Hang)-----------------------
if object_id('sp_TraCuuHangTheoTen', 'p') is not null
drop proc sp_TraCuuHangTheoTen
go
create proc sp_TraCuuHangTheoTen
@tenhang nvarchar(50)
as
begin tran
	set tran isolation level serializable
	select * from hang with(readcommitted)
	where tenhang like N'%'+@tenhang+'%'
	
	waitfor delay'0:00:5'

	update hang
	set soluong = 0	
	where tenhang like N'%'+@tenhang+'%'
commit

exec sp_TraCuuHangTheoTen '5'
-------------end Conversion Deadlock  (Hang)-----------------------


-------------Start Cyclic Deadlock  (Chuyen)-----------------------
if object_id('sp_CapNhatTuyen', 'p') is not null
drop proc sp_CapNhatTuyen
go
create proc sp_CapNhatTuyen
@machuyen1 int, @giokhoihanh1 datetime, @matuyen1 int,
@machuyen2 int, @giokhoihanh2 datetime, @matuyen2 int
as
begin tran		
	update chuyen with(tablock)  set giokhoihanh= @giokhoihanh1, matuyen=@matuyen1 where machuyen=@machuyen1
	waitfor delay'0:0:5'
	update chuyen set giokhoihanh= @giokhoihanh2, matuyen=@matuyen2 where machuyen=@machuyen2
commit



-------------end Cyclic Deadlock  (Chuyen)-----------------------
